package rsj.service.report.dynamic;

import org.springframework.stereotype.Component;
import rsj.api.report.entity.ReportParam;

/**
 * 接口分类报表动态sql
 *
 * @author yuanqinglong
 * @since 2021/7/27 9:32
 */
@Component
public class ProductCategoryReportDynamicStatement {


    //**********************************接口分类报表*************************************//


    /**
     * 接口分类报告列出sql
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String productCategoryReportListSql(ReportParam reportParam) {
        return this.productCategoryReportExportSql(reportParam) + " OFFSET #{pageOffset} LIMIT #{pageSize}";
    }


    /**
     * 接口分类报告导出sql
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String productCategoryReportExportSql(ReportParam reportParam) {
        return "SELECT\n" +
                "p.prod_type_fid,prty_fname,\n" +
                "SUM(stat_req_num) AS stat_req_num,\n" +
                "SUM(stat_findout_num) AS stat_findout_num,\n" +
                "SUM(stat_fail_num) AS stat_fail_num\n" +
                "FROM sys.sys_stat sta\n" +
                "LEFT JOIN sys.sys_prod p ON p.prod_uid = sta.stat_prod_uid\n" +
                "LEFT JOIN sys.sys_prty sp ON sp.prty_fid = p.prod_type_fid" +
                " WHERE stat_date BETWEEN #{startDate} AND #{endDate} GROUP BY p.prod_type_fid,sp.prty_fname";
    }


    /**
     * 接口分类报告统计sql
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String productCategoryReportCountSql(ReportParam reportParam) {
        return "SELECT COUNT(*) FROM (\n" +
                "  SELECT p.prod_type_fid\n" +
                "  FROM sys.sys_stat sta\n" +
                "  LEFT JOIN sys.sys_prod p ON p.prod_uid = sta.stat_prod_uid\n" +
                "  LEFT JOIN sys.sys_prty sp ON sp.prty_fid = p.prod_type_fid\n" +
                "  WHERE stat_date BETWEEN #{startDate} AND #{endDate}  GROUP BY p.prod_type_fid\n" +
                ") a";
    }


    /**
     * 接口分类报告详情sql
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String productCategoryReportDetailListSql(ReportParam reportParam) {
        return this.productCategoryReportDetailExportSql(reportParam) + " OFFSET #{pageOffset} LIMIT #{pageSize}";
    }


    /**
     * 接口分类报告详情导出
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String productCategoryReportDetailExportSql(ReportParam reportParam) {
        String grouping = reportParam.statisticalTimeTypeGrouping();
        return "SELECT\n" +
                "TO_CHAR(stat_date, '" + grouping + "') AS stat_date,p.prod_type_fid,\n" +
                "pt.prty_fname,\n" +
                "SUM(stat_req_num) AS stat_req_num,\n" +
                "SUM(stat_findout_num) AS stat_findout_num,\n" +
                "SUM(stat_fail_num) AS stat_fail_num\n" +
                "FROM sys.sys_stat sta\n" +
                "LEFT JOIN sys.sys_prod p ON p.prod_uid = sta.stat_prod_uid\n" +
                "LEFT JOIN sys.sys_prty pt ON pt.prty_fid = p.prod_type_fid" +
                " WHERE stat_date BETWEEN #{startDate} AND #{endDate} AND prod_type_fid = #{prodTypeFid}" +
                " GROUP BY p.prod_type_fid,pt.prty_fname, TO_CHAR(stat_date, '" + grouping + "')";
    }


    /**
     * 接口分类报告详情统计sql
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String productCategoryReportDetailCountSql(ReportParam reportParam) {
        String grouping = reportParam.statisticalTimeTypeGrouping();
        return "SELECT COUNT(*) FROM (\n" +
                " SELECT p.prod_type_fid\n" +
                " FROM sys.sys_stat sta\n" +
                " LEFT JOIN sys.sys_prod p ON p.prod_uid = sta.stat_prod_uid\n" +
                " LEFT JOIN sys.sys_prty pt ON pt.prty_fid = p.prod_type_fid" +
                "  WHERE stat_date BETWEEN #{startDate} AND #{endDate} AND prod_type_fid = #{prodTypeFid} " +
                " GROUP BY p.prod_type_fid , TO_CHAR(stat_date, '" + grouping + "')" +
                ") a";
    }


}
